﻿using NPOI.SS.UserModel;
using System.Data;
using System.Text.RegularExpressions;

namespace ExcelExport
{
    public class AnalyseExcel
    {
        private string excelDir = string.Empty;

        private GenerateFile gFile = new GenerateFile();
        private List<string> excelPathList = new List<string>();
        private IFormulaEvaluator evalor = null;
        private List<TableField> tableFieldList = new List<TableField>();

        public AnalyseExcel(string exceDirPath)
        {
            excelDir = exceDirPath;
            gFile.Init();
        }

        public void AnalysisTable()
        {
            string excelDirPath = Path.IsPathRooted(excelDir) ? excelDir : Path.Combine(Environment.CurrentDirectory, excelDir);
            //备份路径
            string tmpPath = Utility.DirectoryOper(excelDirPath);
            //获取仅是表格的文件
            var excelPathArray = Directory.GetFiles(excelDirPath, "*.*", SearchOption.AllDirectories).Where(s => s.EndsWith(".xlsx") || s.EndsWith(".xlsm") || s.EndsWith(".xls"));
            excelPathList.Clear();

            foreach (string excelPath in excelPathArray)
            {
                //剔除正在编辑的缓存文件
                if (Path.GetFileName(excelPath).StartsWith("~$"))
                    continue;
                string excelName = Path.GetFileName(excelPath);
                string newPath = Path.Combine(tmpPath, excelName);
                //由于正在编译的文件无法读取，所以先复制所有表到同级目录的*tmp
                File.Copy(excelPath, newPath, true);
                excelPathList.Add(newPath);
            }

            //先解析每一张表
            foreach (string excelPath in excelPathList)
            {
                string excelName = Path.GetFileNameWithoutExtension(excelPath);
                ReadExcelData(excelPath, excelName);
            }

            DirectoryInfo di = new DirectoryInfo(tmpPath);
            di.Delete(true);
            Debug.Error("完成一共,{0}张表", excelPathList.Count);
        }

        private void ReadExcelData(string excelPath, string excelName)
        {
            IWorkbook workbook = null;
            workbook = Utility.ReadExcel(excelPath, ref evalor);
            if (workbook != null)
            {
                AnalyseWorkbook(workbook, excelName);
            }
        }

        private void AnalyseWorkbook(IWorkbook workbook, string excelName)
        {
            if (workbook.NumberOfSheets <= 0)
                return;
            //导出子表
            ISheet sheet;
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                if (!Util.IsSubTable && i >= 1)
                    continue;
                sheet = workbook.GetSheetAt(i);
                if (sheet == null)
                {
                    Debug.Error("{0} 表 sheet name : {1} 读取错误,此表数据将不会被导出，继续导出下一张表", excelName, sheet.SheetName);
                    continue;
                }

                ExecuteAnalyseTable(excelName, sheet);
            }
        }

        private void ExecuteAnalyseTable(string excelName, ISheet sheet)
        {
            string className = "";
            DataTable dataTable = AnalyseDataTable(excelName, sheet, ref className);
            if (dataTable == null)
                return;

            gFile.Generate(className, tableFieldList, dataTable);
        }

        //读取数据
        private DataTable? AnalyseDataTable(string excelName, ISheet sheet, ref string sheetName)
        {
            Release();
            DataTable dataTable = new DataTable();
            DataRow dataRow = null;
            int rowCount = sheet.LastRowNum + 1;

            Debug.Log("处理: {0}  子表  {1}", excelName, sheet.SheetName);
            if (rowCount <= 0)
            {
                Debug.Error("{0}子表 ： {1} 是一张空表，没有导出数据", excelName, sheet.SheetName);
                return null;
            }
            if (rowCount <= Util.beginRow)
            {
                Debug.Error("{0}子表 ： {1} 没有数据，没有导出数据", excelName, sheet.SheetName);
                return null;
            }
            AnalysisFieldList(sheet, ref sheetName);

            if (tableFieldList.Count <= 0)
            {
                return null;
            }

            //先设置列数
            for (int i = Util.beginColumn; i < tableFieldList.Count + 1; i++)
            {
                DataColumn dc = new DataColumn();
                dataTable.Columns.Add(dc);
            }

            bool isAdd = false;
            //处理数据
            for (int i = Util.beginDataRow; i < rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                    continue;
                if (row.Cells.Count <= 0)
                    continue;

                //标记为del的行不导出
                if (Utility.GetCellValue(row.Cells[0], evalor).ToLower().Equals("del"))
                    continue;

                dataRow = dataTable.NewRow();
                for (int j = 1; j < tableFieldList.Count + 1; j++)
                {
                    TableField field = tableFieldList[j - 1];
                    //得到当前单元格
                    ICell cell = row.GetCell(field.colInx, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    string val = Utility.GetCellValue(cell, evalor);

                    //字段定义为int类型的，如果表填写的是小数四舍五入
                    if (field.fieldType == TableType.INT)
                    {
                        if (val.Contains('.'))
                        {
                            float v1 = float.Parse(val);
                            val = Convert.ToInt32(v1).ToString();
                        }
                    }

                    //对数组类型进行数据判断
                    if (Check(field, val, cell))
                        dataRow[j - 1] = ConvertVal(field, val);
                    else
                    {
                        Debug.Error("{0}表 sheet name = {1} 第{2}行第{3}列数据类型错误，将继续导出下一张表", excelName, cell.Sheet.SheetName, cell.RowIndex + 1, cell.ColumnIndex);
                        return null;//字段类型检测错误直接返回，继续下一张表
                    }
                }

                dataTable.Rows.Add(dataRow);
            }
            return dataTable;
        }

        //读取表头
        private void AnalysisFieldList(ISheet sheet, ref string rSheetName)
        {
            ICell cSheetName = sheet.GetRow(0).GetCell(1);
            ICell cNeedExport = sheet.GetRow(0).GetCell(2);

            string needExport = Utility.GetCellValue(cNeedExport, evalor);
            string sheetName = Utility.GetCellValue(cSheetName, evalor);

            rSheetName = sheetName;

            if (!Utility.IsValidSheets(sheetName))
            {
                return;
            }

            if (!(needExport == "1" || needExport == "true"))
            {
                return;
            }

            IRow needRow = null;
            IRow nameRow = null;
            IRow typeRow = null;
            IRow descRow = null;

            needRow = sheet.GetRow(1);//是否导出
            nameRow = sheet.GetRow(2);//Name(Key)
            typeRow = sheet.GetRow(3);//Type
            descRow = sheet.GetRow(4);//Desc(注释)

            //以type或者name为主，如果没有这两项，则意味不需要导出的字段
            int index = 0;
            for (int i = 1; i < typeRow.LastCellNum; i++)
            {
                ICell cNeed = needRow.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                ICell cName = nameRow.LastCellNum > i ? nameRow.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK) : null;
                ICell cType = typeRow.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                ICell cDesc = descRow.LastCellNum > i ? descRow.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK) : null;

                //判断type和name都不为空
                string needVal = Utility.GetCellValue(cNeed, evalor);
                string typeVal = Utility.GetCellValue(cType, evalor);
                string nameVal = Utility.GetCellValue(cName, evalor);

                if (needVal != "1" || string.IsNullOrEmpty(typeVal) || string.IsNullOrEmpty(nameVal))
                    continue;

                //属性名称和字段不能是中文
                if (Utility.IsChinese(typeVal) || Utility.IsChinese(nameVal))
                    continue;

                TableField tableField = new TableField();

                tableField.SetFieldType(Utility.GetCellValue(cType, evalor));
                tableField.explainStr = Utility.GetCellValue(cDesc, evalor);
                tableField.filedName = Utility.GetCellValue(cName, evalor).Replace("\n", "").Replace(" ", "").Replace("\t", "").Replace("\r", "");
                tableField.colInx = i;

                tableFieldList.Add(tableField);
            }
        }

        private string ConvertVal(TableField field, string val)
        {
            string result = val;
            if (string.IsNullOrEmpty(result))
            {
                if (field.isList)
                {
                    result = "null";
                }
                else
                {
                    switch (field.fieldType)
                    {
                        case TableType.BYTE:
                        case TableType.SHORT:
                        case TableType.INT:
                        case TableType.FLOAT:
                        case TableType.ULONG:
                        case TableType.BOOL:
                            result = "0";//默认为0
                            break;
                        case TableType.STRING:
                            result = "";
                            break;
                    }
                }
            }
            if (result.EndsWith(".0"))
            {
                result = result.Substring(0, result.Length - 2);
            }
            result = ConvertType(field, result);
            return result;
        }

        private string ConvertType(TableField field, string objectStr)
        {
            string result = string.Empty;
            switch (field.fieldType)
            {
                case TableType.BOOL:
                    if (objectStr.Equals("0") || objectStr.Equals("False"))
                    {
                        objectStr = "false";
                    }
                    if (objectStr.Equals("1") || objectStr.Equals("True"))
                    {
                        objectStr = "true";
                    }
                    break;
                case TableType.STRING:
                    objectStr = objectStr.Replace("\n", "");
                    objectStr = objectStr.Replace(Util.DATA_SEP, "");
                    break;
            }
            result = objectStr;
            return result;
        }

        private bool Check(TableField field, string val, ICell cell)
        {
            bool result = true;
            if (field.isList)
            {
                string[] valArr = val.Split('~');
                //string list 什么值都转成string , 其他要判断类型是否正确
                if (field.fieldType != TableType.STRING && !string.IsNullOrEmpty(val))
                {
                    for (int k = 0; k < valArr.Length; k++)
                    {
                        if (field.fieldType != TableType.BOOL)
                        {
                            if (!IsNumeric(valArr[k]))
                                result = false;//表格类型错误
                        }
                        if (!CheckType(valArr[k], field, cell))
                        {
                            result = false;//表格类型错误
                        }
                    }
                }
            }
            else
            {
                if (field.fieldType != TableType.STRING && field.fieldType != TableType.BOOL && !string.IsNullOrEmpty(val) && !string.IsNullOrWhiteSpace(val))
                {
                    if (field.fieldType != TableType.BOOL)
                    {
                        if (!IsNumeric(val))
                            result = false;//表格类型错误
                    }
                    if (!CheckType(val, field, cell))
                    {
                        result = false;//表格类型错误
                    }

                }
            }
            return result;
        }

        private bool CheckType(string value, TableField field, ICell cell)
        {
            //throw new Exception(); 有错误直接执行下一张导表，不抛出异常
            bool result = true;
            switch (field.fieldType)
            {
                case TableType.BYTE:
                    int v = int.Parse(value);
                    if (!(v <= 127 && v >= 0))
                    {
                        Debug.Error("byte数据超过（0-127）范围 第{0}行，{1}列", cell.RowIndex + 1, cell.ColumnIndex);
                        result = false;
                    }
                    break;
                case TableType.BOOL:
                    if (!value.ToLower().Equals("true") && !value.ToLower().Equals("false") && !value.Equals("0") && !value.Equals("1"))
                    {
                        Debug.Error("bool 只能填(false，true)不许区分大小写，0（false），1（true）第{0}行，{1}列", cell.RowIndex + 1, cell.ColumnIndex);
                        result = false;
                    }
                    break;
            }
            return result;
        }

        private bool IsNumeric(string value)
        {
            return Regex.IsMatch(value, @"^[+-]?\d*[.]?\d*$");
        }

        private void Release()
        {
            tableFieldList.Clear();
        }
    }
}
